package querysrc;



public class sqlscripts {
	public static final String sql_get_max_user_id = "SELECT max(user_id) as id FROM foodbase.users ";
	public static final String sql_get_max_user_id_c1 =  "id";
	public static final String sql_get_max_visitor_id = "SELECT max(visitor_id) as id FROM foodbase.visitors";
	
	
	//reg visitor
	public static final String sql_reg_visitor_select_v_uid = "SELECT visitor_id FROM foodbase.visitors " +
			"where " +
			"imsi=? and imei=?";
	public static final String sql_reg_visitor_select_v_uid_c1 = "visitor_id";
			
	public static final String sql_reg_visitor_insert = "INSERT INTO foodbase.visitors " +
			"(visitor_id,imsi,imei,phone_number,first_use_time, birth, gender) " +
			"VALUES" +
			"(?,?,?,?,?,?,?)";
	//delete visitor
	public static final String sql_del_visitor = "delete FROM foodbase.visitors " +
			"where visitor_id=?";
	
	
	
	//reg user
	public static final String sql_reg_user_select_uid = "SELECT user_id,imsi,imei,passwd,birth,gender,ts " +
			"FROM foodbase.users where email=?";
	public static final String sql_reg_user_select_uid_c1_uid="user_id";
	public static final String sql_reg_user_select_uid_c2_imsi="imsi";
	public static final String sql_reg_user_select_uid_c3_imei="imei";
	public static final String sql_reg_user_select_uid_c4_passwd="passwd";
	public static final String sql_reg_user_select_uid_c5_birth="birth";
	public static final String sql_reg_user_select_uid_c6_gender="gender";
	public static final String sql_reg_user_select_uid_c7_ts="ts";
	
	
	public static final String sql_reg_user_insert_uid = "INSERT INTO foodbase.users " +
			"(user_id,imsi,imei,phone_number,first_use_time,birth,gender,email,passwd,first_reg_time,ts)" +
			" VALUES " +
			" (?,?,?,?,?,?,?,?,?,?,?)";	
	//search visitor existing
	public static final String sql_reg_user_visitor_check = "SELECT imsi,imei,first_use_time FROM foodbase.visitors " +
			"where visitor_id=?";
	public static final String sql_reg_user_visitor_check_c1_imsi="imsi";
	public static final String sql_reg_user_visitor_check_c2_imei="imei";
	public static final String sql_reg_user_visitor_check_c3_first_use_time="first_use_time";
	
	//update user mobile info
	
	public static final String sql_login_user_update = "update foodbase.users " +
			"set imsi=?,imei=? " +
			"where user_id=?";
	public static final String sql_login_user_change_birth ="update foodbase.users " +
			"set birth=?, gender=?, ts=? " +
			"where user_id=?";
	
	//feeds record insert
	public static final String sql_insert_feeds_record = "INSERT INTO foodbase.feeds(food_id , user_id , r_date , r_time , quantity ,ts ) " +
			"VALUES (?,?,?,?,?,?)";
	//feeds get max timestamp
	public static final String sql_feeds_max_ts_select = "SELECT max(ts) as ts FROM foodbase.feeds where user_id=?";
	public static final String sql_feeds_max_ts_select_c1 = "ts";
	
	//feeds record insert
	public static final String sql_insert_h_record = "INSERT INTO foodbase.huser(user_id ,height, ts ) " +
			"VALUES (?,?,?)";
	public static final String sql_insert_w_record = "INSERT INTO foodbase.wuser(user_id ,weight,ts ) " +
	"VALUES (?,?,?)";
	//feeds get max timestamp
	public static final String sql_h_max_ts_select = "SELECT max(ts) as ts FROM foodbase.huser where user_id=?";
	public static final String sql_hw_max_ts_select_c1 = "ts";
	public static final String sql_w_max_ts_select = "SELECT max(ts) as ts FROM foodbase.wuser where user_id=?";
	
	
	//get imei, imsi for a given user_id or visitor_id
	public static final String sql_authen_user_select = "SELECT imsi,imei FROM foodbase.users " +
			"where user_id=?";
	public static final String sql_authen_user_select_c1_imsi = "imsi";
	public static final String sql_authen_user_select_c2_imei = "imei";
	
	public static final String sql_authen_user_select2 = "SELECT imsi,imei FROM foodbase.visitors " +
			"where visitor_id=?";
	
	//feeds sync
	public static final String sql_feeds_get_latest_select = "SELECT food_id,r_time,quantity,ts  FROM foodbase.feeds " +
			"where user_id=? and DATEDIFF(CURDATE(),r_time)<=? and ts>?";
	public static final String sql_feeds_get_latest_select_c1_food_id = "food_id";
	public static final String sql_feeds_get_latest_select_c2_r_time = "r_time";
	public static final String sql_feeds_get_latest_select_c3_quantity = "quantity";
	public static final String sql_feeds_get_latest_select_c4_ts = "ts";
	
	public static final int feeds_max_latest_days  = 7;
	
	//food_prop sync
	public static final String sql_food_prop_sync = "SELECT food_prop_id,food_prop,UNIX_TIMESTAMP(ts) as ts FROM foodbase.food_prop ";
	public static final String sql_food_prop_sync_c1_food_prop_id = "food_prop_id";
	public static final String sql_food_prop_sync_c2_food_prop = "food_prop";
	public static final String sql_food_prop_sync_c3_ts = "ts";
	//foods sync
	public static final String sql_foods_sync = "SELECT food_id,food_prop_id,foodname,unit,category,UNIX_TIMESTAMP(ts) as ts FROM foodbase.foods ";
	public static final String sql_foods_sync_c1_food_id = "food_id";
	public static final String sql_foods_sync_c2_food_prop_id = "food_prop_id";
	public static final String sql_foods_sync_c3_foodname = "foodname";
	public static final String sql_foods_sync_c4_unit = "unit";
	public static final String sql_foods_sync_c5_category = "category";
	public static final String sql_foods_sync_c6_ts = "ts";
	
	//get last_avg today
	public static final String sql_last_avg_get_today = "SELECT v.food_prop_id, v.r_date, CONCAT(v.food_prop,'(',v.unit,')') as food_prop ,  u.quantity as yours, v.quantity as avgs, (u.quantity-v.quantity) as diffs " +
			"FROM foodbase.user_last_avg u join foodbase.last_avg v " +
			"on  v.r_date = u.r_date and v.month_age = u.month_age and v.gender=u.gender and v.food_prop_id = u.food_prop_id " +
			"where u.user_id = ? and datediff(CURDATE(),v.r_date)=1";
		
		//"SELECT v.food_prop_id, v.r_date, CONCAT(v.food_prop,'(',v.unit,')') as food_prop,  u.quantity as yours, v.quantity as avgs, u.quantity-v.quantity as diffs FROM foodbase.user_last_avg u join foodbase.last_avg v " +
			//"where  v.r_date = u.r_date and v.month_age = u.month_age and v.gender=u.gender and u.user_id = ? and datediff(CURDATE(),v.r_date)=1";
	public static final String sql_last_avg_get_today_c1_food_prop_id = "food_prop_id";
	public static final String sql_last_avg_get_today_c2_r_date = "r_date";
	public static final String sql_last_avg_get_today_c3_food_prop = "food_prop";
	public static final String sql_last_avg_get_today_c4_yours = "yours";
	public static final String sql_last_avg_get_today_c5_avgs = "avgs";
	public static final String sql_last_avg_get_today_c6_diffs = "diffs";

	//SELECT v.r_date, v.food_prop, v.food_prop_id, u.quantity as yours, v.quantity as avgs, u.quantity-v.quantity as diffs FROM foodbase.user_last_avg u join foodbase.last_avg v where  v.r_date = u.r_date and v.month_age = u.month_age and u.user_id = 1 and datediff(CURDATE(),v.r_date)=1
	
	//get birth for user_id
	public static final String sql_get_birth_month = "SELECT TIMESTAMPDIFF(MONTH,birth,curdate()) as age FROM foodbase.users " +
			"where user_id=?";
	public static final String sql_get_birth_month_c1 = "age";
	public static final String sql_get_birth_month2 = "SELECT TIMESTAMPDIFF(MONTH,birth,curdate()) as age FROM foodbase.visitors " +
			"where visitor_id=?";
	
	//get tips today
	public static final String sql_get_tip_today = "SELECT content,s_date FROM foodbase.tips " +
			"where s_id=? and datediff(CURDATE(),s_date)=0";
	public static final String sql_get_tip_today_c1_c = "content";
	public static final String sql_get_tip_today_c2_uid = "s_date";
	
	
	//feedbacks
	public static final String sql_insert_feedbacks = "insert into foodbase.feedbacks (user_id,r_time,content) " +
			"values(?,?,?)";
	
	//weibo
	public static final String sql_insert_weibo = "insert into foodbase.weibo(user_id,weibo_id,weibo_account,credential,ts) " +
			"values (?,1,?,?,?);";
	
	public static final String sql_delete_weibo = "delete from foodbase.weibo where user_id=?";
	public static final String sql_logout_user = "update foodbase.users set imsi='dummy', imei='dummy' where user_id=?";
	
	public static final String sql_get_avg_height = "select age, gender, min_h as min, mid_h as mid, max_h as max, UNIX_TIMESTAMP(ts) as ts from foodbase.avg_height";
	public static final String sql_get_avg_weight = "select age, gender, min_w as min, mid_w as mid, max_w as max, UNIX_TIMESTAMP(ts) as ts from foodbase.avg_weight";
	public static final String sql_get_h_default = "select age, gender, d1, d2,d3,d4,d5,min,max,UNIX_TIMESTAMP(ts) as ts from foodbase.h_default";
	public static final String sql_get_w_default = "select age, gender, d1, d2,d3,d4,d5,min,max,UNIX_TIMESTAMP(ts) as ts from foodbase.w_default";
	public static final String sql_get_h_ux = "select d1,d2,d3,d4,d5,min,max,UNIX_TIMESTAMP(ts) as ts from foodbase.h_ux" +
			"where user_id=?";
	public static final String sql_get_w_ux = "select d1, d2,d3,d4,d5,min,max,UNIX_TIMESTAMP(ts) as ts from foodbase.w_ux" +
			"where user_id=?";
	public static final String sql_get_food_ux = "select food_id,d1, d2,d3,d4,d5,min,max,UNIX_TIMESTAMP(ts) as ts from foodbase.food_ux" +
	"where user_id=?";
	public static final String sql_get_food_default = "select age,gender,food_id,d1, d2,d3,d4,d5,min,max,UNIX_TIMESTAMP(ts) as ts from foodbase.food_default";
	public static final String sql_get_hw_age = "age";
	public static final String sql_get_hw_gender = "gender";
	public static final String sql_get_hw_d1 = "d1";
	public static final String sql_get_hw_d2 = "d2";
	public static final String sql_get_hw_d3 = "d3";
	public static final String sql_get_hw_d4 = "d4";
	public static final String sql_get_hw_d5 = "d5";
	public static final String sql_get_hw_min = "min";
	public static final String sql_get_hw_max = "max";
	public static final String sql_get_hw_ts = "ts";
	public static final String sql_get_food_id = "food_id";
	public static final String sql_get_min = "min";
	public static final String sql_get_mid = "mid";
	public static final String sql_get_max = "max";
	
	
	public static final String sql_get_weibo ="SELECT weibo_account,ts FROM foodbase.weibo where user_id=?";
	public static final String sql_get_weibo_c1_wc="weibo_account";
	public static final String sql_get_weibo_c2_ts="ts";
	
	//getts
	public static final String sql_get_ts_feedbacks = "SELECT max(UNIX_TIMESTAMP(r_time)) as ts , count(r_time) as c " +
			"FROM foodbase.feedbacks where user_id = ?";
	
	public static final String sql_get_ts_feeds = "SELECT max(ts) as ts , count(ts) as c " +
			"FROM foodbase.feeds where user_id =? ";
	
	public static final String sql_get_ts_food_prop = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
			"FROM foodbase.food_prop ";
	public static final String sql_get_ts_foods = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
			"FROM foodbase.foods ";
	public static final String sql_get_ts_h_default = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
			"FROM foodbase.h_default ";
	public static final String sql_get_ts_w_default = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
	"FROM foodbase.w_default ";
	public static final String sql_get_ts_food_default = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
	"FROM foodbase.food_default ";
	
	public static final String sql_get_ts_geth = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
	"FROM foodbase.avg_height ";
	public static final String sql_get_ts_getw = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
	"FROM foodbase.avg_weight ";
	
	public static final String sql_get_ts_h_ux = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
	"FROM foodbase.h_ux where user_id = ?";
	public static final String sql_get_ts_w_ux = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
	"FROM foodbase.w_ux where user_id = ?";
	public static final String sql_get_ts_food_ux = "SELECT max(UNIX_TIMESTAMP(ts)) as ts , count(ts) as c " +
	"FROM foodbase.food_ux where user_id = ?";
	public static final String sql_get_ts_last_avg = "select max(UNIX_TIMESTAMP(r_date)) as ts, count(user_id) as c " +
			"from foodbase.user_last_avg where user_id = ?";
	public static final String sql_get_ts_tips = "SELECT max(UNIX_TIMESTAMP(s_date)) as ts, count(unique_id) as c " +
			"FROM foodbase.tips where  s_id=? and datediff(CURDATE(),s_date)=0";
	public static final String sql_get_ts_contact = "SELECT max(UNIX_TIMESTAMP(ts)) as ts, count(ts) as c FROM foodbase.ourcontact";
	public static final String sql_get_ts_loadinfo = "SELECT max(UNIX_TIMESTAMP(ts)) as ts, count(ts) as c FROM foodbase.loadinfo";
	public static final String sql_get_ts_user = "SELECT max(ts) as ts, count(ts) as c FROM foodbase.users " +
			"where user_id=?";
	public static final String sql_get_ts_addh = "SELECT max(ts) as ts, count(ts) as c FROM foodbase.huser " +
	"where user_id=?";
	public static final String sql_get_ts_addw = "SELECT max(ts) as ts, count(ts) as c FROM foodbase.wuser " +
	"where user_id=?";
	
	
	public static final String sql_get_ts_weibo = "SELECT max(ts) as ts, count(ts) as c FROM foodbase.weibo " +
	"where user_id=?";
	public static final String sql_get_ts_c1_ts = "ts";
	public static final String sql_get_ts_c2_count = "c";
	
	public static final String sql_get_contact = "SELECT name,value,UNIX_TIMESTAMP(ts) as ts " +
			"FROM foodbase.ourcontact";
	public static final String sql_get_contact_c1_name = "name";
	public static final String sql_get_contact_c2_value = "value";
	public static final String sql_get_contact_c3_ts = "ts";
	public static final String sql_get_loadinfo = "SELECT content,UNIX_TIMESTAMP(ts) as ts FROM foodbase.loadinfo";
	public static final String sql_get_loadinfo_c1_content="content";
	public static final String sql_get_loadinfo_c2_ts = "ts";
}
